const Database = require('better-sqlite3');
const path = require('path');

const dbPath = path.join(__dirname, 'data', 'delivery-management.db');
const db = new Database(dbPath, { readonly: true });

console.log('=== COOLING WATER SPRAY SYSTEM TABLE ANALYSIS ===\n');

// 1. Table schema
console.log('1. Table Schema:');
const schema = db.prepare('PRAGMA table_info(device_instances_piping_cooling_water_spray_system)').all();
schema.forEach(col => {
  console.log(`  ${col.name}: ${col.type}${col.notnull ? ' NOT NULL' : ''}${col.pk ? ' (PRIMARY KEY)' : ''}`);
});

// 2. Check for any data
console.log('\n2. Record Count:');
const count = db.prepare('SELECT COUNT(*) as count FROM device_instances_piping_cooling_water_spray_system').get();
console.log(`  Total records: ${count.count}`);

// 3. Sample data if any exists
if (count.count > 0) {
  console.log('\n3. Sample Data (all records):');
  const data = db.prepare('SELECT * FROM device_instances_piping_cooling_water_spray_system').all();
  data.forEach((row, index) => {
    console.log(`  Record ${index + 1}:`);
    Object.keys(row).forEach(key => {
      if (row[key] !== null && row[key] !== '') {
        console.log(`    ${key}: ${row[key]}`);
      }
    });
    console.log('');
  });
} else {
  console.log('\n3. No data found in table');
}

// 4. Check related parameter tables
console.log('\n4. Related Parameter Tables Check:');
const paramTables = ['instance_params_1_table_c2', 'instance_params_1_table_c6', 'instance_params_1_table_c494'];
paramTables.forEach(table => {
  try {
    const paramCount = db.prepare(`SELECT COUNT(*) as count FROM ${table}`).get();
    console.log(`  ${table}: ${paramCount.count} records`);
    
    if (paramCount.count > 0) {
      const sampleParam = db.prepare(`SELECT * FROM ${table} LIMIT 1`).get();
      console.log(`    Sample columns: ${Object.keys(sampleParam).join(', ')}`);
    }
  } catch (error) {
    console.log(`  ${table}: Error - ${error.message}`);
  }
});

// 5. Check instance_pbs_mapping for cooling references
console.log('\n5. PBS Mapping for cooling:');
try {
  const coolingPbs = db.prepare(`SELECT * FROM instance_pbs_mapping WHERE instance_id LIKE '%cool%'`).all();
  console.log(`  Found ${coolingPbs.length} cooling-related PBS mappings:`);
  coolingPbs.forEach(row => {
    console.log(`    Instance: ${row.instance_id}, PBS Level: ${row.pbs_level_id}, PBS Instance: ${row.pbs_instance_id}`);
  });
} catch (error) {
  console.log(`  Error checking PBS mapping: ${error.message}`);
}

db.close();